package avicit.im.lantop.archiveplanmanager.archiveplanedit.action.service.impl;

import avicit.cbb.tabledefine.service.CommTableManagerIntfsService;
import avicit.cbb.undomessage.service.UndoMessageManagerIntfsService;
import avicit.im.lantop.archiveplanmanager.archiveplanedit.action.service.ArchivePlanService;
import avicit.im.lantop.archiveplanmanager.archiveplanedit.domain.*;
import avicit.im.lantop.archiveplanmanager.archiveplanedit.service.ArchivePlanService;
import avicit.im.lantop.docmanager.mydocmanager.domain.LantopSubmit;
import avicit.im.lantop.psncenter.mybrowse.domain.LantopBrowseRecord;
import avicit.platform6.bpm.web.service.BpmOperateService;
import avicit.platform6.commons.utils.ComUtil;
import avicit.platform6.commons.utils.PojoUtil;
import avicit.platform6.commons.utils.web.TreeNode;
import avicit.platform6.core.dao.hibernate.CommonHibernateDao2;
import avicit.platform6.core.domain.BeanBase;
import avicit.platform6.core.domain.BeanProcess;
import avicit.platform6.core.exception.DaoException;
import avicit.platform6.core.jdbc.JdbcAvicit;
import avicit.platform6.core.properties.PlatformConstant.OpResult;
import avicit.platform6.core.properties.PlatformConstant.OpType;
import avicit.platform6.core.service.ServiceBase;
import avicit.platform6.generic.constant.CommonFunction;
import avicit.platform6.modules.system.syslog.service.SysLogUtil;
import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.servlet.ModelAndView;

import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
@SuppressWarnings("deprecation")
public class ArchivePlanServiceImpl extends ServiceBase implements ArchivePlanService {

    @Autowired
    private CommonHibernateDao2 hibernateDao;

    @Autowired
    private JdbcAvicit jdbcAvicit;

    @Override
    public void delete(BeanBase arg0) throws DaoException {
        // TODO Auto-generated method stub

    }

    @Override
    public void insert(BeanBase arg0) throws DaoException {
        // TODO Auto-generated method stub

    }

    @Override
    public void update(BeanBase arg0) throws DaoException {
        // TODO Auto-generated method stub

    }

    /**
     * 添加计划树节点
     */
    @Override
    public void addPlanTreeNode(ArchivePlanTree plantree) {
        // 设置节点类型 默认1
        plantree.setNodeType(1);
        plantree.setIsDel(1);

        plantree.setQzh(plantree.getOrgId());
        String nodeId = ComUtil.getId();
        plantree.setId(nodeId);
        PojoUtil.setSysProperties(plantree, OpType.insert);
        // 保存节点
        hibernateDao.save(plantree);
        SysLogUtil.log(plantree, null, OpType.insert, OpResult.success);
    }

    /**
     * 获取归档计划节点
     *
     * @param
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> getPlanTreeNode(String orgId, String id) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT * FROM LANTOP_ARCHIVE_PLAN_TREE");
        if (id == null || "".equals(id)) {
            // 说明是根节点获取数据
            sbSql.append(" WHERE IS_DEL=1 AND ORG_ID='" + orgId + "' AND PID IS NULL ORDER BY NODE_SORT ASC,CREATION_DATE DESC");
        } else {
            sbSql.append(" WHERE IS_DEL=1 AND ORG_ID='" + orgId + "' AND PID = '" + id + "' ORDER BY NODE_SORT ASC,CREATION_DATE DESC");
        }
        List<Map<String, Object>> queryForList = this.jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString());
        return queryForList;
    }

    /**
     * 根据id获取父节点数据
     *
     * @param id 节点id
     * @return
     * @throws Exception
     */
    @Override
    public Map<String, Object> findPNodeIdByNodeId(String id) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT * FROM LANTOP_ARCHIVE_PLAN_TREE WHERE ID='");
        sbSql.append(id);
        sbSql.append("'");
        Map<String, Object> queryForMap = this.jdbcAvicit.getJdbcTemplate().queryForMap(sbSql.toString());
        return queryForMap;
    }

    /**
     * 修改计划节点
     *
     * @param
     * @return
     * @throws Exception
     */
    @Override
    public void editNodeIdByNodeId(ArchivePlanTree plantree) {
        this.jdbcAvicit.getJdbcTemplate().update("UPDATE LANTOP_ARCHIVE_PLAN_TREE SET NODE_NAME='" + plantree.getNodeName() + "',NODE_SORT=" + plantree.getNodeSort() + " WHERE ID='" + plantree.getId() + "'");

    }


    /**
     * 删除节点
     *
     * @param
     * @return
     * @throws Exception
     */
    @Override
    public Integer delNodeIdByIds(String ids) {
        String[] split = ids.split(",");
        AjaxResult result = new AjaxResult();
        result.setData(false);
        for (int i = 0; i < split.length; i++) {
            isDataNode(split[i], result);
            if ((boolean) result.getData()) {
                return 0;
            }
        }
        for (int i = 0; i < split.length; i++) {
            this.jdbcAvicit.getJdbcTemplate().update("UPDATE LANTOP_ARCHIVE_PLAN_TREE SET IS_DEL=2 WHERE ID IN(SELECT ID FROM LANTOP_ARCHIVE_PLAN_TREE START WITH ID='" + split[i] + "' CONNECT BY PRIOR ID=PID)");
        }
        return 1;
    }

    /**
     * 根据节点id查询是否是数据节点
     *
     * @param nodeId
     * @return
     */
    public Boolean isDataNode(String nodeId, AjaxResult result) {
        Boolean isDataNode = false;
        //查询当前节点是否是数据节点
        Map<String, Object> queryForMap = this.jdbcAvicit.getJdbcTemplate().queryForMap("SELECT ID,NODE_TYPE FROM LANTOP_ARCHIVE_PLAN_TREE WHERE IS_DEL=1 AND ID='" + nodeId + "'");
        if ("2".equals(queryForMap.get("NODE_TYPE") + "")) {
            result.setData(true);
            return true;
        }
        //查询当前节点子节点
        List<Map<String, Object>> queryForList = this.jdbcAvicit.getJdbcTemplate().queryForList("SELECT ID,NODE_TYPE FROM LANTOP_ARCHIVE_PLAN_TREE WHERE IS_DEL=1 AND PID='" + nodeId + "'");
        if (queryForList != null && queryForList.size() > 0) {
            for (int i = 0; i < queryForList.size(); i++) {
                if ("2".equals(queryForList.get(i).get("NODE_TYPE") + "")) {
                    // 说明是数据节点
                    result.setData(true);
                    return isDataNode;
                }
                isDataNode = isDataNode(queryForList.get(i).get("ID") + "", result);
            }
        }
        return isDataNode;
    }

    /**
     * 加载归档计划节点树
     *
     * @param planTree
     * @return List<TreeNode>
     * @throws Exception
     * @throws
     * @author XMW
     */
    @Override
    public List<TreeNode> getArchivePlanNodeTree(ArchivePlanTree planTree) {
        int level = 1;// 树展开的层级
        if (planTree.getId() == null || "".equals(planTree.getId())) {
            level = 2;
        }
        List<TreeNode> nodeList = new ArrayList<TreeNode>();
        TreeNode node = new TreeNode();
        if (planTree.getId() == null || "".equals(planTree.getId())) {
            // 如果id为空 设置父节点数据
            node.setId("");
            node.setIconCls("icon-im-newnorg");
            node.setText("归档计划管理目录");
            node.setState("open");
            node.setChildren(getTreeNodeToDataNodeInfoByInfs(planTree, level));
            nodeList.add(node);
            return nodeList;
        } else {
            return getTreeNodeToDataNodeInfoByInfs(planTree, level);
        }
    }

    /**
     * 加载归档计划节点树的子节点
     *
     * @param planTree
     * @return AjaxResult
     * @throws Exception
     * @throws
     * @author XMW
     */
    public List<TreeNode> getTreeNodeToDataNodeInfoByInfs(ArchivePlanTree planTree, Integer level) {
        List<TreeNode> nodeList = new ArrayList<TreeNode>();
        if (level == 0) {
            return null;
        }
        level--;
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT ID,NODE_NAME,PID,NODE_TYPE,ORG_ID FROM LANTOP_ARCHIVE_PLAN_TREE WHERE  IS_DEL=1 ");
        sbSql.append(" AND ORG_ID ='");
        sbSql.append(planTree.getOrgId());
        sbSql.append("' ");
        if (planTree.getId() == null || "".equals(planTree.getId())) {
            sbSql.append(" AND PID IS NULL");
        } else {
            sbSql.append(" AND PID='");
            sbSql.append(planTree.getId());
            sbSql.append("' ");
        }
        sbSql.append(" ORDER BY NODE_SORT ASC,CREATION_DATE DESC");
        List<Map<String, Object>> queryForList = this.jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString());

        for (int i = 0; i < queryForList.size(); i++) {
            ArchivePlanTree planTree1 = new ArchivePlanTree();
            planTree1.setId((String) queryForList.get(i).get("ID"));
            planTree1.setpId((String) queryForList.get(i).get("PID"));
            planTree1.setOrgId((String) queryForList.get(i).get("ORG_ID"));

            TreeNode node = new TreeNode();
            node.setId((String) queryForList.get(i).get("ID"));

            node.setText((String) queryForList.get(i).get("NODE_NAME"));
            if ("2".equals(queryForList.get(i).get("NODE_TYPE") + "")) {
                node.setIconCls("icon-im-newndatanode");
            } else {
                node.setIconCls("icon-im-newnmodulelib");
                if (findNodeIsLastById(node.getId(), "", queryForList.get(i).get("ORG_ID") + "")) {
                    if (level > 0) {
                        node.setState("open");
                        node.setChildren(this.getTreeNodeToDataNodeInfoByInfs(planTree1, level));
                    } else {
                        node.setState("closed");
                    }
                }
            }
            nodeList.add(node);
        }
        return nodeList;

    }

    /**
     * 查询节点是否是最后节点
     *
     * @param
     * @return
     * @throws Exception
     */
    @Override
    public Boolean findNodeIsLastById(String id, String sysId, String orgId) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT COUNT(1) FROM LANTOP_ARCHIVE_PLAN_TREE WHERE IS_DEL=1 AND ORG_ID='");
        sbSql.append(orgId);
        sbSql.append("' AND PID='");
        sbSql.append(id);
        sbSql.append("'");
        int count = this.jdbcAvicit.getJdbcTemplate().queryForInt(sbSql.toString());
        return count > 0;
    }

    /**
     * 加载归档计划数据表格
     *
     * @param
     * @return
     * @throws Exception
     */
    @Override
    public Map<String, Object> getPlanByNoid(String nodeId, String sysId, String orgId, Integer page, Integer rows, String whereSql, String roleSql) {
        Map<String, Object> pageData = new HashMap<>();
        // 获得当前节点有效计划总数
        StringBuffer sbSql1 = new StringBuffer();
        sbSql1.append("SELECT COUNT(1) FROM LANTOP_ARCHIVE_PLAN T WHERE IS_DEL=1 AND ORG_ID='");
        sbSql1.append(orgId);
        sbSql1.append("' ");
        if (roleSql != null && !"".equals(roleSql)) {
            sbSql1.append(roleSql);
        }
        sbSql1.append("AND NODE_ID='");
        sbSql1.append(nodeId);
        sbSql1.append("' AND SYS_ID='");
        sbSql1.append(sysId);
        sbSql1.append("'");
        /*if (whereSql != null && !"".equals(whereSql)) {
            sbSql1.append(" AND PLAN_NAME ");
            sbSql1.append(whereSql);
        }*/
        sbSql1.append(whereSql);
        Long total = this.jdbcAvicit.getJdbcTemplate().queryForLong(sbSql1.toString());
        pageData.put("total", total);

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM (SELECT ROW_NUMBER() OVER(ORDER BY CREATION_DATE DESC) SORT,T.* FROM LANTOP_ARCHIVE_PLAN T WHERE IS_DEL=1 AND ORG_ID='");
        sbSql.append(orgId);
        sbSql.append("' ");
        if (roleSql != null && !"".equals(roleSql)) {
            sbSql1.append(roleSql);
        }
        sbSql.append(" AND NODE_ID='");
        sbSql.append(nodeId);
        sbSql.append("' AND SYS_ID='");
        sbSql.append(sysId);
        sbSql.append("'  ");
        /*if (whereSql != null && !"".equals(whereSql)) {
            sbSql.append(" AND PLAN_NAME ");
            sbSql.append(whereSql);
        }*/
        sbSql.append(whereSql);
        sbSql.append(" ) ROW_ WHERE ROWNUM <");

        sbSql.append(page * rows + 1);
        sbSql.append(" ) WHERE ROWNUM_ >");
        sbSql.append((page - 1) * rows);

        List<Map<String, Object>> rowsList = this.jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString());
        pageData.put("rows", rowsList);
        return pageData;
    }

    /**
     * 添加归档计划
     *
     * @param param
     * @return
     * @throws Exception
     */
    @Override
    public void saveArchivePlan(Map<String, Object> param) throws ParseException {

        if ("save".equals(param.get("operationType") + "")) {
            // 保存计划
            ArchivePlan plan = new ArchivePlan();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            if (param.get("ARCHIVE_DATE") != null && !"".equals(param.get("ARCHIVE_DATE") + "")) {
                plan.setArchiveDate(sdf.parse((String) param.get("ARCHIVE_DATE")));
            }
            plan.setPlanName((String) param.get("PLAN_NAME"));
            plan.setPlanNum((String) param.get("PLAN_NUMBER"));
            plan.setId(ComUtil.getId());
            // 新添数据默认未删除,未发布
            plan.setIsDel(1);
            plan.setStatus(1);
            plan.setFlowStatus(1);
            plan.setPublishUser(param.get("PUBLISH_USER") == null ? "" : param.get("PUBLISH_USER") + "");
            plan.setPublishUserId(param.get("PUBLISH_USER_ID") == null ? "" : param.get("PUBLISH_USER") + "");

            plan.setQzh((String) param.get("orgId"));
            plan.setOrgId((String) param.get("orgId"));
            plan.setSysId((String) param.get("sysId"));
            plan.setNodeId((String) param.get("nodeId"));

            PojoUtil.setSysProperties(plan, OpType.insert);
            // 保存节点
            hibernateDao.save(plan);
            SysLogUtil.log(plan, null, OpType.insert, OpResult.success);

            // 更新节点信息
            this.jdbcAvicit.getJdbcTemplate().update("UPDATE LANTOP_ARCHIVE_PLAN_TREE SET NODE_TYPE=2 WHERE ID='" + param.get("nodeId") + "'");

        } else {
            // 编辑计划
            this.jdbcAvicit.getJdbcTemplate().update(
                    "UPDATE LANTOP_ARCHIVE_PLAN SET PLAN_NUMBER='" + param.get("PLAN_NUMBER") + "',PLAN_NAME='" + param.get("PLAN_NAME") + "',ARCHIVE_DATE= TO_DATE('" + param.get("ARCHIVE_DATE") + "','yyyy-mm-dd') where id='" + param.get("planId") + "'");
        }
    }

    /**
     * 添加项目
     *
     * @param param
     * @return
     * @throws Exception
     */
    @Override
    public void saveArchivePlanItem(Map<String, Object> param) throws ParseException {
        ArchivePlanItem planItem = new ArchivePlanItem();
        planItem.setId(ComUtil.getId());
        planItem.setDutyUser((String) param.get("dutyUser"));
        planItem.setDutyUserId((String) param.get("dutyUserId"));
        planItem.setDutyDept((String) param.get("dutyDept"));
        planItem.setDutyDeptId((String) param.get("dutyDeptId"));
        planItem.setPlanId((String) param.get("planId"));
        planItem.setItemName((String) param.get("itemName"));
        planItem.setIsDel(1);
        planItem.setStatus(1);
        planItem.setFlowStatus(1);
        planItem.setQzh((String) param.get("orgId"));
        planItem.setOrgId((String) param.get("orgId"));
        planItem.setSysId((String) param.get("sysId"));
        PojoUtil.setSysProperties(planItem, OpType.insert);
        planItem.setCreationDate((Date) param.get("creationDate"));
        // 保存节点
        hibernateDao.save(planItem);
        SysLogUtil.log(planItem, null, OpType.insert, OpResult.success);
    }

    /**
     * 编辑项目
     *
     * @param param
     * @return
     * @throws Exception
     */
    @Override
    public int editArchivePlanItem(Map<String, Object> param) {
        int i = this.jdbcAvicit.getJdbcTemplate().update(
                "UPDATE LANTOP_ARCHIVE_PLAN_ITEM SET ITEM_NAME='" + param.get("itemName") + "',DUTY_USER='" + param.get("dutyUser") + "',DUTY_USER_ID='" + param.get("dutyUserId") + "',DUTY_DEPT='" + param.get("dutyDept") + "',DUTY_DEPT_ID='" + param.get("dutyDeptId")
                        + "' WHERE ID='" + param.get("taskId") + "'");
        return i;
    }

    /**
     * 获取项目下的计划表格
     *
     * @param
     * @return
     * @throws Exception
     */
    @Override
    public Map<String, Object> getTaskReocrdByPlanId(String planId, String sysId, String orgId, Integer page, Integer rows, String whereSql) {
        Map<String, Object> pageData = new HashMap<>();
        // 获得当前节点有效计划总数
        StringBuffer sbSql1 = new StringBuffer();
        sbSql1.append("SELECT COUNT(1) FROM LANTOP_ARCHIVE_PLAN_ITEM WHERE IS_DEL=1 AND ORG_ID='");
        sbSql1.append(orgId);
        sbSql1.append("' AND PLAN_ID='");
        sbSql1.append(planId);
        sbSql1.append("' AND SYS_ID='");
        sbSql1.append(sysId);
        sbSql1.append("'");
       /* if (whereSql != null && !"".equals(whereSql)) {
            sbSql1.append(" AND ITEM_NAME ");
        }*/
        sbSql1.append(whereSql);
        Long total = this.jdbcAvicit.getJdbcTemplate().queryForLong(sbSql1.toString());
        pageData.put("total", total);

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM (SELECT  ROW_NUMBER() OVER(ORDER BY CREATION_DATE DESC) SORT,T.* FROM LANTOP_ARCHIVE_PLAN_ITEM T WHERE IS_DEL=1 AND ORG_ID='");
        sbSql.append(orgId);
        sbSql.append("' AND PLAN_ID='");
        sbSql.append(planId);
        sbSql.append("' AND SYS_ID='");
        sbSql.append(sysId);
        sbSql.append("' ");
        /*if (whereSql != null && !"".equals(whereSql)) {
            sbSql.append(" AND ITEM_NAME ");

        }*/
        sbSql.append(whereSql);
        sbSql.append(") ROW_ WHERE ROWNUM <");
        sbSql.append(page * rows + 1);
        sbSql.append(" ) WHERE ROWNUM_ >");
        sbSql.append((page - 1) * rows);

        List<Map<String, Object>> rowsList = this.jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString());
        pageData.put("rows", rowsList);
        return pageData;
    }

    /**
     * 删除任务
     *
     * @param
     * @throws Exception
     * @throws
     * @author xmw
     */
    @Override
    public int delTaskByIds(String ids) {
        String[] split = ids.split(",");
        StringBuffer sbSql = new StringBuffer();
        for (int i = 0; i < split.length; i++) {
            sbSql.append("UPDATE LANTOP_ARCHIVE_PLAN_ITEM SET IS_DEL=2 WHERE ID='");
            sbSql.append(split[i]);
            sbSql.append("'");
            int update = this.jdbcAvicit.getJdbcTemplate().update(sbSql.toString());
            if (update < 0) {
                return update;
            }
            sbSql.setLength(0);
        }
        return 1;
    }

    /**
     * 删除计划
     *
     * @param
     * @throws Exception
     * @throws
     * @author xmw
     */
    @Override
    public int delPlanByIds(String ids) {
        String[] split = ids.split(",");
        StringBuffer sbSql = new StringBuffer();
        for (int i = 0; i < split.length; i++) {
            // 删除计划
            sbSql.append("UPDATE LANTOP_ARCHIVE_PLAN SET IS_DEL=2 WHERE ID='");
            sbSql.append(split[i]);
            sbSql.append("'");
            int update = this.jdbcAvicit.getJdbcTemplate().update(sbSql.toString());
            if (update < 0) {
                return update;
            }
            sbSql.setLength(0);
            // 删除计划对应的项目信息
            sbSql.append("UPDATE LANTOP_ARCHIVE_PLAN_ITEM SET IS_DEL=2 WHERE PLAN_ID='");
            sbSql.append(split[i]);
            sbSql.append("'");
            update = this.jdbcAvicit.getJdbcTemplate().update(sbSql.toString());
            if (update < 0) {
                return update;
            }
            sbSql.setLength(0);
        }
        return 1;
    }

    /**
     * 根据id发布计划
     *
     * @param
     * @throws Exception
     * @throws
     * @author xmw
     */
    @Override
    public int publishPlan(String ids, String userId, String userName) {
        String[] split = ids.split(",");
        StringBuffer sbSql = new StringBuffer();
        for (int i = 0; i < split.length; i++) {
            // 发布计划
            sbSql.append("UPDATE LANTOP_ARCHIVE_PLAN SET STATUS=2,PUBLISH_USER_ID='");
            sbSql.append(userId);
            sbSql.append("',PUBLISH_USER='");
            sbSql.append(userName);
            sbSql.append("',PUBLISH_DATE=SYSDATE");
            sbSql.append(" WHERE ID='");
            sbSql.append(split[i]);
            sbSql.append("'");
            int update = this.jdbcAvicit.getJdbcTemplate().update(sbSql.toString());
            if (update < 0) {
                return update;
            }
            sbSql.setLength(0);
        }
        return 1;
    }

    /**
     * 设置计划项目已归档
     *
     * @param
     * @throws
     * @author xmw
     */
    @Override
    public int setTaskStatus(String ids) {
        String[] split = ids.split(";");
        StringBuffer sbSql = new StringBuffer();
        for (int i = 0; i < split.length; i++) {
            sbSql.append("UPDATE LANTOP_ARCHIVE_PLAN_ITEM SET STATUS=2 WHERE ID='");
            sbSql.append(split[i]);
            sbSql.append("'");
            int update = this.jdbcAvicit.getJdbcTemplate().update(sbSql.toString());
            if (update < 0) {
                return update;
            }
            sbSql.setLength(0);
        }
        return 1;
    }

    /**
     * 刷新归档计划归档时间和归档率
     *
     * @param
     * @throws
     * @author xmw
     */

    private Map<String, Object> refreshPlanRow(String planId, Integer submitCount) {
        Map<String, Object> resultMap = new HashMap<>();
        // 获取当前计划下的所有项目数
        long allCountObj = this.jdbcAvicit.getJdbcTemplate().queryForLong("SELECT COUNT(1) FROM LANTOP_ARCHIVE_PLAN_ITEM WHERE IS_DEL=1 AND PLAN_ID = '" + planId + "'");
        Double.parseDouble(allCountObj + "");
        // 获取当前计划下已归档的项目数
        long rateCountObj = this.jdbcAvicit.getJdbcTemplate().queryForLong("SELECT COUNT(1) FROM LANTOP_ARCHIVE_PLAN_ITEM WHERE IS_DEL=1 AND STATUS=3 AND PLAN_ID = '" + planId + "'");
        double rateCount = Double.parseDouble((rateCountObj + submitCount) + "");
        double rate = rateCount / allCountObj * 100;
        Object arhiveRate = rate >= 0 ? new BigDecimal(rate).setScale(2, BigDecimal.ROUND_HALF_UP) : 0;

        // 更新计划表的归档率和归档时间
        this.jdbcAvicit.getJdbcTemplate().update("UPDATE LANTOP_ARCHIVE_PLAN SET ARCHIVE_ACT_DATE = SYSDATE , ARCHIVE_RATE = " + arhiveRate + " WHERE ID='" + planId + "'");

        resultMap.put("ARCHIVE_RATE", arhiveRate);
        resultMap.put("ARCHIVE_ACT_DATE", System.currentTimeMillis());
        return resultMap;
    }

    /**
     * 获取计划模板
     *
     * @param
     * @throws
     * @author xmw
     */
    @Override
    public List<Map<String, Object>> getTemplate(String orgId, String sysId) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT ID,ARCHIVE_FILE_KIND_NAME FROM LANTOP_ARCHIVE_FILE_KIND WHERE PARENT_ID='ROOT' AND ARCHIVE_FILE_KIND_CODE='plan' AND ORG_ID='" + orgId + "' AND SYS_ID='" + sysId + "' ORDER BY CREATION_DATE ASC,SORT_NUMBER ASC");
        List<Map<String, Object>> queryForList = this.jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString());
        return queryForList;
    }

    /**
     * 根据模板查询其下的项目
     *
     * @param
     * @throws
     * @author xmw
     */
    @Override
    public Map<String, Object> getTemplateTask(String orgId, String sysId, String id, Integer page, Integer rows) {
        Map<String, Object> pageData = new HashMap<>();
        // 获得当前节点有效计划总数
        StringBuffer sbSql1 = new StringBuffer();
        sbSql1.append("SELECT COUNT(1) FROM LANTOP_ARCHIVE_FILE_KIND WHERE  ORG_ID='");
        sbSql1.append(orgId);
        sbSql1.append("' AND PARENT_ID='");
        sbSql1.append(id);
        sbSql1.append("' AND SYS_ID='");
        sbSql1.append(sysId);
        sbSql1.append("'");

        Long total = this.jdbcAvicit.getJdbcTemplate().queryForLong(sbSql1.toString());
        pageData.put("total", total);

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM (SELECT  ROW_NUMBER() OVER(ORDER BY CREATION_DATE ASC,SORT_NUMBER ASC) SORT,ID,ARCHIVE_FILE_KIND_NAME FROM LANTOP_ARCHIVE_FILE_KIND WHERE  ORG_ID='");
        sbSql.append(orgId);
        sbSql.append("' AND PARENT_ID='");
        sbSql.append(id);
        sbSql.append("' AND SYS_ID='");
        sbSql.append(sysId);
        sbSql.append("' ");
        sbSql.append(") ROW_ WHERE ROWNUM <");
        sbSql.append(page * rows + 1);
        sbSql.append(" ) WHERE ROWNUM_ >");
        sbSql.append((page - 1) * rows);

        List<Map<String, Object>> rowsList = this.jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString());
        pageData.put("rows", rowsList);

        return pageData;
    }

    /**
     * 按模板添加
     *
     * @param
     * @throws
     * @author xmw
     */
    @Override
    public void setTaskTemplate(String orgId, String sysId, String planId, String modelId) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT  ID,ARCHIVE_FILE_KIND_NAME,CREATION_DATE FROM LANTOP_ARCHIVE_FILE_KIND WHERE  ORG_ID='");
        sbSql.append(orgId);
        sbSql.append("' AND PARENT_ID='");
        sbSql.append(modelId);
        sbSql.append("' AND SYS_ID='");
        sbSql.append(sysId);
        sbSql.append("' ");

        sbSql.append("  ORDER BY CREATION_DATE desc,SORT_NUMBER ASC ");

        List<Map<String, Object>> rowsList = this.jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString());
        Map<String, Object> param = new HashMap<>();
        param.put("planId", planId);
        param.put("sysId", sysId);
        param.put("orgId", orgId);
        try {
            for (int i = 0; i < rowsList.size(); i++) {
                param.put("itemName", rowsList.get(i).get("ARCHIVE_FILE_KIND_NAME"));
                param.put("creationDate", rowsList.get(i).get("CREATION_DATE"));
                saveArchivePlanItem(param);
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }

    @Autowired
    private BpmOperateService bpmOperateService;

    @Autowired
    private UndoMessageManagerIntfsService undoMessageManagerIntfsService;


    /**
     * 设置归档完结
     *
     * @param parameter
     * @return
     */
    @Override
    @SuppressWarnings("unchecked")
    public Map<String, Object> setTaskArchiveEnd(LantopSubmit ls, Map<String, Object> parameter) throws Exception {
        ls.setId(ComUtil.getId());
        if (null == parameter) {
            throw new Exception("启动流程失败,请传递流程启动参数");
        }
        String processDefId = (String) parameter.get("processDefId");
        String formCode = (String) parameter.get("formCode");
        String userId = (String) parameter.get("userId");
        String deptId = (String) parameter.get("deptId");
        String processInstanceId = null; // 流程实例ID
        String taskUrl = ""; // 待办url
        if (processDefId == null || processDefId.equals("")) {
            throw new Exception("启动流程失败，缺少流程定义ID！");
        }
        Map<String, Object> variables = new HashMap<>();
        // 把表单对象转换成map,传递给流程变量
        Map<String, Object> pojoMap = (Map<String, Object>) PojoUtil.toMap(ls);
        // 流程待办标题
        variables.putAll(pojoMap);
        variables.put("f_todo_title", ls.getSubmitTitile());

        processInstanceId = bpmOperateService.startProcessInstanceById(processDefId, formCode, userId, deptId, variables);
        // // 取待办URL，如果不需要直接打开可以注释以下代码//
        taskUrl = bpmOperateService.getTaskUrl(processInstanceId, userId);
        // 返回对象
        BeanProcess bp = new BeanProcess();
        bp.setFormId(ls.getId());
        bp.setProcessInstanceId(processInstanceId);
        bp.setTaskUrl(URLEncoder.encode(taskUrl, "utf-8"));

        //添加流程送审表信息
        PojoUtil.setSysProperties(ls, OpType.insert);
        this.hibernateDao.save(ls);
        SysLogUtil.log(ls, null, OpType.insert, OpResult.success);


        //更新送审计划信息
        String taskIds = parameter.get("taskIds") == null ? "" : parameter.get("taskIds") + "";
        if (taskIds != null && !"".equals(taskIds)) {
            String[] planIdArrs = taskIds.split(",");
            Session session = hibernateDao.getSession();
            for (String planId : planIdArrs) {
                ArchivePlanItem archivePlanItem = (ArchivePlanItem) session.get(ArchivePlanItem.class, planId);
                archivePlanItem.setFlowStatus(2);
                archivePlanItem.setFlowId(ls.getId());
                session.update(archivePlanItem);
            }

        }
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("APPLY_TYPE", "1");
        map.put("APPLY_TITLE", ls.getSubmitTitile());
        map.put("APPLY_USER_ID", ls.getSubmitUserId());
        map.put("APPLY_DEPT_ID", ls.getSubmitDeptId());
        map.put("FORM_ID", ls.getId());
        map.put("RECORD_DETAIL_URL", taskUrl);
        map.put("TABLE_NAME", "LANTOP_SUBMIT");
        map.put("R_TABLE_NAME", "LANTOP_ARCHIVE_PLAN_ITEM");
        map.put("ENTRY_ID", processInstanceId);
        List<Map<String, Object>> undoList = new ArrayList<Map<String, Object>>();
        undoList.add(map);
        this.undoMessageManagerIntfsService.insertRecordByBatchApplyByIntfs(parameter.get("orgId") + "", deptId, parameter.get("sysId") + "", undoList);

        Map<String, Object> reusltMap = new HashMap<>();
        reusltMap.put("bp", bp);
        reusltMap.put("id", ls.getId());

        return reusltMap;
    }


    /**
     * 根基流程送审表的id获取送审流程计划数据
     *
     * @param id
     * @return
     */
    @Override
    @SuppressWarnings("unchecked")
    public Map<String, Object> getArchiveSubmintPlanByFlowId(Integer page, Integer rows, String id) {
        Map<String, Object> pageData = new HashMap<>();
        //获取评价总数
        long total = jdbcAvicit.getJdbcTemplate().queryForLong("SELECT COUNT(1) FROM LANTOP_ARCHIVE_PLAN_ITEM WHERE IS_DEL=1 and  ARCHIVE_FLOW_ID='" + id + "' AND ARCHIVE_FLOW_STATUS=2");
        String hql = " from ArchivePlanItem where isDel=1 and flowId=? and flowStatus=2";
        Query query = hibernateDao.createQuery(hql);
        query.setParameter(0, id);
        query.setMaxResults(page * rows + 1);
        query.setFirstResult((page - 1) * rows);
        List<ArchivePlanItem> list = query.list();
        pageData.put("rows", list);
        pageData.put("total", total);
        return pageData;
    }


    /**
     * 流程审批完成修改审批计划状态
     *
     * @param formId
     */
    @Override
    @SuppressWarnings("unchecked")
    public void updateSubmitStatus(String formId) {
//        refreshPlanRow
        String hql = " from ArchivePlanItem where isDel=1 and flowId=? and flowStatus=2";
        List<ArchivePlanItem> list = hibernateDao.createQuery(hql, formId).list();
        Session session = hibernateDao.getSession();
        if (list != null && list.size() > 0) {
            for (ArchivePlanItem archivePlanItem : list) {
                ArchivePlanItem archivePlanItem1 = (ArchivePlanItem) session.get(ArchivePlanItem.class, archivePlanItem.getId());
                archivePlanItem1.setFlowStatus(3);
                archivePlanItem1.setStatus(3);
                session.update(archivePlanItem1);
            }
        }
        //更新计划归档率
        refreshPlanRow(list.get(0).getPlanId(), list.size());

    }

    /**
     * 根据送审Id获取送审项目所在计划
     *
     * @param id
     * @return
     */
    @Override
    public ArchivePlan getPlanByFromId(String id) {
        Query query = hibernateDao.getSession().createQuery("from ArchivePlanItem where isDel=1 and flowId=?");
        query.setParameter(0, id);
        List<ArchivePlanItem> archivePlanItem = query.list();
        ArchivePlan ArchivePlan = null;
        if (archivePlanItem != null && archivePlanItem.size() > 0) {
            ArchivePlan = (ArchivePlan) hibernateDao.getSession().get(ArchivePlan.class, archivePlanItem.get(0).getPlanId());
        }
        return ArchivePlan;
    }

    /**
     * @Author XMW
     * @Description: 获取我的待办数据列表信息
     * @Param:
     * @Return:
     * @Create: 2019/6/17  11:22
     */
    @Override
    public Map<String, Object> getMytodos(String userId, String taskTitle, String taskFinished, String falg, String orgId, Integer page, Integer rows) {
        Map<String, Object> result = new HashMap<>();

        String sql = "select count(1) from (SELECT MAX(t3.APPLY_TITLE)  \"taskTitle\" , t.EXECUTION_,t.PROCINST_,t.TASK_B_ID_ FROM BPM_HIST_TASK_V t   LEFT JOIN BPM_ENTRY_V t1 ON t.TASK_B_ID_=t1.FORMID_  inner join CBB_MY_UNDO_APPLY  t3 ON t3.FORM_ID = t.TASK_B_ID_";
        sql += " WHERE  (t3.ORG_ID='" + orgId + "'  OR  t3.ATTRIBUTE_08 IS NOT NULL) AND t.TASK_STATE_='1' AND t.TASK_FINISHED_='" + taskFinished + "' AND t.ASSIGNEE_ ='" + userId + "' ";
        if ("1".equals(falg)) {
            sql += "AND t1.BUSINESSSTATE_ <> 'ended' ";
        }
        if ("2".equals(falg)) {
            sql += "AND t1.BUSINESSSTATE_= 'ended' ";
        }
        sql += " GROUP BY t.EXECUTION_,t.PROCINST_,t.TASK_B_ID_ ) A1 ";
        if (taskTitle != null && !taskTitle.equals(ComUtil.EMPTY)) {
            sql += " WHERE A1.\"taskTitle\" like '%" + taskTitle + "%'";
        }
        Long total = this.jdbcAvicit.getJdbcTemplate().queryForLong(sql);
        result.put("total", total);

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM (");
        sbSql.append(" SELECT ROW_NUMBER () OVER ( ORDER BY A1.\"cTime\" DESC ) \"sort\",A1.* FROM (");
        sbSql.append("SELECT  MAX(t.DBID_) \"dbid\", MAX(t3.APPLY_TITLE) \"taskTitle\",t.EXECUTION_ \"executionId\",MIN(t1.START_) \"cTime\",t.PROCINST_ \"processInstance\",t.TASK_B_ID_ \"businessId\",MAX(t.TASK_TITLE_) \"taskTitle1\",MAX(t4.NAME) \"taskSendUser\",MAX(t.TASK_SENDDEPT_) \"taskSendDept\",MAX(t.FORM_) \"formResourceName\",MAX(t.PROCESS_DEF_NAME_) \"processDefName\",MAX(t1.BUSINESSSTATE_) BUSINESSSTATE_,MAX( t.FORM_CODE_ ) \"formCode\" FROM BPM_HIST_TASK_V t   LEFT JOIN BPM_ENTRY_V t1 ON t.TASK_B_ID_=t1.FORMID_  inner join CBB_MY_UNDO_APPLY t3 ON t3.FORM_ID = t.TASK_B_ID_  LEFT JOIN SYS_USER t4 ON t4.id=t3.CREATED_BY ");
        sbSql.append(" WHERE  (t3.ORG_ID='" + orgId + "' OR  t3.ATTRIBUTE_08 IS NOT NULL) AND t.TASK_STATE_='1' AND t.TASK_FINISHED_='");
        sbSql.append(taskFinished);
        sbSql.append("' AND t.ASSIGNEE_ ='");
        sbSql.append(userId);
        sbSql.append("' ");
        if ("1".equals(falg)) {
            sbSql.append(" AND t1.BUSINESSSTATE_<>'ended' ");
        }
        if ("2".equals(falg)) {
            sbSql.append(" AND t1.BUSINESSSTATE_='ended' ");
        }
        sbSql.append(" GROUP BY t.EXECUTION_,t.PROCINST_,t.TASK_B_ID_) A1 ");
        if (taskTitle != null && !taskTitle.equals(ComUtil.EMPTY)) {
            sbSql.append(" WHERE A1.\"taskTitle\" LIKE '%" + taskTitle + "%' ");
        }
        sbSql.append(") ROW_ WHERE ROWNUM <");
        sbSql.append(page * rows + 1);

        sbSql.append(" ) WHERE ROWNUM_ >");
        sbSql.append((page - 1) * rows);
        System.out.println(sbSql.toString());
        List<Map<String, Object>> maps = jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString());
        result.put("rows", maps);
        return result;
    }


    /**
     * 归档计划关联预归档文件
     *
     * @param fileNodeId
     * @param fileIds
     * @param taskId
     * @return
     */
    @Override
    public Map<String, Object> saveFlieRelationTask(String orgId, String deptId, String sysId, String fileNodeId, String fileIds, String taskId,String isAn,String searchSbSql) {
        Map<String, Object> result = new HashMap<>();
        try {
            String tableName = "";
            String antableName="";
            List<Map<String, Object>> nodeList = this.commTableManagerIntfsService.getCbbTableInfoByNodeIdByIntfs(orgId, deptId, sysId, fileNodeId, "");
            if (nodeList != null && nodeList.size() > 0) {
                if (nodeList.size() == 1) {
                    tableName = nodeList.get(0).get("TABLE_NAME") == null ? "" : nodeList.get(0).get("TABLE_NAME").toString();
                }
                if (nodeList.size() == 2) {
                    antableName = nodeList.get(0).get("TABLE_NAME") == null ? "" : nodeList.get(0).get("TABLE_NAME").toString();
                    tableName = nodeList.get(1).get("TABLE_NAME") == null ? "" : nodeList.get(1).get("TABLE_NAME").toString();
                }
            }
            String anIds = fileIds.replaceAll(";", "','");
            if ("1".equals(isAn)){
                    List<Map<String, Object>> mapListWJ = jdbcAvicit.getJdbcTemplate().queryForList("select id from  "+tableName +" where 1=1 "+searchSbSql+" AND NODE_ID = '"+fileNodeId+"' \n" +
                            "\t\tAND PARENT_ID in ('"+anIds+"') \n" +
                            "\t\tAND ( ARCHIVE_STATUS = '0' ) \n" +
                            "\t\tAND ( ARCHIVE_FLOW_STATUS = '1' OR ARCHIVE_FLOW_STATUS = '' OR ARCHIVE_FLOW_STATUS IS NULL ) \n" +
                            "\t\tAND ARCHIVE_SHARE_STATUS = '2' \n" +
                            "\tORDER BY\n" +
                            "\t\tCREATION_DATE DESC ");
                    if (mapListWJ!=null&&mapListWJ.size()>0){
                        fileIds="";
                        for (Map<String, Object> map : mapListWJ) {
                            fileIds+=";"+map.get("ID");
                        }
                        fileIds = fileIds.substring(1);
                    }

            }



            ArchivePlanItem archivePlanItem = (ArchivePlanItem) hibernateDao.getSession().get(ArchivePlanItem.class, taskId);
            String attribute04 = archivePlanItem.getAttribute04();
            Map<String, Object> map = null;
            if (attribute04 != null && !"".equals(attribute04)) {
                map = (Map<String, Object>) JSON.parseObject(attribute04, Map.class);
                if (map != null && map.size() > 0) {
                    if (map.containsKey(fileNodeId)) {
                        String saveFileIds = map.get(fileNodeId) == null ? "" : map.get(fileNodeId).toString();
                        if (saveFileIds != null && !"".equals(saveFileIds)) {
                            String[] split = fileIds.split(";");
                            for (String s : split) {

                                if (saveFileIds.indexOf(s) < 0) {
                                    saveFileIds += ";" + s;
                                }
                            }
                        } else {
                            saveFileIds = fileIds;
                        }
                        map.put(fileNodeId, saveFileIds);
                    } else {
                        map.put(fileNodeId, fileIds);
                    }
                }

            } else {
                map = new HashMap<>();
                map.put(fileNodeId, fileIds);
            }
            archivePlanItem.setAttribute04(JSON.toJSONString(map));
            hibernateDao.getSession().update(archivePlanItem);
            //修改对应数据表的plan_status字段状态

            if ("1".equals(isAn)){
                jdbcAvicit.getJdbcTemplate().update("update " + antableName + " set PLAN_STATE='2' where id in ('" + anIds + "')");
            }
            String[] split = fileIds.split(";");
            for (String fileId : split) {
                jdbcAvicit.getJdbcTemplate().update("update " + tableName + " set PLAN_STATE='2' where id='" + fileId + "'");
            }
            result.put("flag", 1);
        } catch (Exception e) {
            result.put("flag", 0);
        }

        return result;
    }

    @Autowired
    private CommTableManagerIntfsService commTableManagerIntfsService;

    /**
     * 获取关联计划的档案文件
     *
     * @param taskId
     * @return
     */
    @Override
    public Map<String, Object> getRelationTaskArchive(String orgId, String deptId, String sysId, String taskId) {
        Map<String, Object> result = new HashMap<>();
        try {
            ArchivePlanItem archivePlanItem = (ArchivePlanItem) hibernateDao.getSession().get(ArchivePlanItem.class, taskId);
            List<Map<String, Object>> mapList = new ArrayList<>();
            if (archivePlanItem != null) {
                String attribute04 = archivePlanItem.getAttribute04();
                if (attribute04 != null && !"".equals(attribute04)) {
                    Map<String, Object> map = JSON.parseObject(attribute04, Map.class);
                    String tableName = "";
                    for (Map.Entry<String, Object> entry : map.entrySet()) {
                        List<Map<String, Object>> nodeList = this.commTableManagerIntfsService.getCbbTableInfoByNodeIdByIntfs(orgId, deptId, sysId, entry.getKey(), "");
                        //= this.deptdocarrangeService.getChildNodeIdByParentId(entry.getKey());
                        if (nodeList != null && nodeList.size() > 0) {
                            if (nodeList.size() == 1) {
                                tableName = nodeList.get(0).get("TABLE_NAME") == null ? "" : nodeList.get(0).get("TABLE_NAME").toString();
                            }
                            if (nodeList.size() == 2) {
                                tableName = nodeList.get(1).get("TABLE_NAME") == null ? "" : nodeList.get(1).get("TABLE_NAME").toString();
                            }
                        }
                        /*String s = entry.getValue() == null ? "" : entry.getValue().toString();
                        s = s.replaceAll(";","','");
*/
                        List<Map<String, Object>> maps = jdbcAvicit.getJdbcTemplate().queryForList("select * from " + tableName + " where id in ('" + entry.getValue().toString().replaceAll(";", "','") + "')");
                        mapList.addAll(maps);
                    }

                }
            }
            result.put("rows", mapList);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public void updateArchiveplan(String formId) throws Exception {
        String sql = "UPDATE LANTOP_ARCHIVE_PLAN_ITEM SET ARCHIVE_FLOW_STATUS='1' WHERE ARCHIVE_FLOW_ID='" + formId + "'";
        jdbcAvicit.getJdbcTemplate().update(sql);
    }


    /**
     * 创建下载信息
     *
     * @param orgId
     * @param deptId
     * @param sysId
     * @param recordId（记录ID）
     * @param downUserId-浏览人
     * @param tableName（物理表名）
     * @throws Exception void
     * @author xmw
     */
    @Override
    public void createMyDownByIntfs(String orgId, String deptId, String sysId, String recordId, String downUserId, String tableName) throws Exception {
        try {
            if (recordId != null && orgId != null) {
                List<Map<String, Object>> mapList = jdbc.getJdbcTemplate().queryForList("select org_code from sys_org where id='" + orgId + "'");
                if (mapList != null && mapList.size() > 0) {
                    String orgCode = mapList.get(0).get("ORG_CODE") + "";
                    List<Map<String, Object>> mapList1 = jdbcAvicit.getJdbcTemplate().queryForList("SELECT LANTOP_NODE_ID,ARCHIVE_TABLE_ID,ARCHIVE_TABLE_NAME FROM LANTOP_DATA_V_" + orgCode + " WHERE LANTOP_RCD_ID='" + recordId + "'");
                    if (mapList1 != null && mapList1.size() > 0) {
                        String nodeId = mapList1.get(0).get("LANTOP_NODE_ID") == null ? "" : mapList1.get(0).get("LANTOP_NODE_ID").toString();
                        String tableId = mapList1.get(0).get("ARCHIVE_TABLE_ID") == null ? "" : mapList1.get(0).get("ARCHIVE_TABLE_ID").toString();
                        tableName = mapList1.get(0).get("ARCHIVE_TABLE_NAME") == null ? "" : mapList1.get(0).get("ARCHIVE_TABLE_NAME").toString();
                        if (tableName != null && !"".equals(tableName) && recordId != null && !"".equals(recordId)) {
                            jdbcAvicit.getJdbcTemplate().update("update " + tableName + " set download_num=nvl(download_num,0)+1 where id='" + recordId + "'");
                        }
                    }
                }
            }

            //SanXiaFiles update by huangjian 2019-10-18 start 不判断是否存在相同的记录
            /*StringBuffer sbSql1 = new StringBuffer();
            sbSql1.append("SELECT LBR.ID,LBR.RECORD_NUM FROM LANTOP_FILE_DOWN_RECORD LBR WHERE LBR.RECORD_ID = '");
            sbSql1.append(recordId);
            sbSql1.append("' AND LBR.DOWN_USER_ID = '");
            sbSql1.append(downUserId);
            sbSql1.append("'  ");
            //SanXiaFiles update by huangjian start 用户多部门情况
            sbSql1.append(" AND LBR.DEPT_ID = '"+deptId+"' ");
            List<Map<String, Object>> list = this.jdbcAvicit.queryForMap(sbSql1.toString(), null);
            if (null != list && list.size() > 0) {
                // 已存在下载记录，更新:下载次数+1,最后下载时间更新
                Map<String, Object> downbrowseMap = list.get(0);
                long recordNum = null == downbrowseMap.get("RECORD_NUM") ? 0 : Long.parseLong((String) downbrowseMap.get("RECORD_NUM"));
                LantopDownRecord lantopDownRecord = new LantopDownRecord();
                lantopDownRecord.setId((String) downbrowseMap.get("ID"));
                lantopDownRecord.setRecordNum((++recordNum));
                lantopDownRecord.setLastDownDate(new Date());
                LantopDownRecord objOld = this.hibernateDao.get(LantopDownRecord.class, lantopDownRecord.getId());
                hibernateDao.evict(objOld);
                LantopDownRecord newBean = new LantopDownRecord();
                PojoUtil.copyProperties(newBean, objOld, true);
                PojoUtil.copyProperties(newBean, lantopDownRecord, true);
                SysLogUtil.log(newBean, objOld, OpType.update, OpResult.success);
                PojoUtil.setSysProperties(newBean, OpType.update);
                hibernateDao.update(newBean);
            } else {*/
                // 不存在下载记录，新增
                LantopDownRecord lantopDownRecord = new LantopDownRecord();
                lantopDownRecord.setId(ComUtil.getId());
                lantopDownRecord.setOrgId(orgId);
                lantopDownRecord.setDeptId(deptId);
                lantopDownRecord.setSysId(sysId);
                lantopDownRecord.setDownUserId(downUserId);
                lantopDownRecord.setRecordId(recordId);
                lantopDownRecord.setLastDownDate(new Date());
                lantopDownRecord.setTableName(tableName);
                lantopDownRecord.setRecordNum(1L);
                PojoUtil.setSysProperties(lantopDownRecord, OpType.insert);
                this.hibernateDao.save(lantopDownRecord);
                SysLogUtil.log(lantopDownRecord, null, OpType.insert, OpResult.success);
            //}


            //SanXiaFiles update by huangjian 如果下载后 没有浏览记录 新增浏览记录默认0  start
            //防止统计不出数据出来
            StringBuffer sbSqlBrowse = new StringBuffer();
            sbSqlBrowse.append("SELECT LBR.ID,LBR.RECORD_NUM FROM LANTOP_BROWSE_RECORD LBR WHERE LBR.RECORD_ID = '");
            sbSqlBrowse.append(recordId);
            sbSqlBrowse.append("' AND LBR.BROWSE_USER_ID = '");
            sbSqlBrowse.append(downUserId);
            sbSqlBrowse.append("'  ");
            //SanXiaFiles update by huangjian start 用户多部门情况
            sbSqlBrowse.append(" AND LBR.DEPT_ID = '"+deptId+"' ");
            List<Map<String, Object>> listBrows = this.jdbcAvicit.queryForMap(sbSqlBrowse.toString(), null);
            if (null != listBrows && listBrows.size() <= 0) {
                // 不存在浏览记录，新增
                LantopBrowseRecord lantopBrowseRecord = new LantopBrowseRecord();
                lantopBrowseRecord.setId(ComUtil.getId());
                lantopBrowseRecord.setOrgId(orgId);
                lantopBrowseRecord.setDeptId(deptId);
                lantopBrowseRecord.setSysId(sysId);
                lantopBrowseRecord.setBrowseUserId(downUserId);
                lantopBrowseRecord.setRecordId(recordId);
                lantopBrowseRecord.setLastBrowseDate(new Date());
                lantopBrowseRecord.setRecordDetailUrl("");
                lantopBrowseRecord.setTableName(tableName);
                lantopBrowseRecord.setRecordNum(0L);
                PojoUtil.setSysProperties(lantopBrowseRecord, OpType.insert);
                this.hibernateDao.save(lantopBrowseRecord);
                SysLogUtil.log(lantopBrowseRecord, null, OpType.insert, OpResult.success);
            }
            //-----------------------------------------------end
        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    @Override
    public  Map<String,Object> moveNode(Map<String, Object> param) throws Exception {
        Map<String,Object> reslut = new HashMap<>();
        String targetNodeId = param.get("targetNodeId").toString();
        String treeId = param.get("treeId").toString();
        List<Map<String, Object>> mapList = jdbcAvicit.getJdbcTemplate().queryForList("select * from LANTOP_ARCHIVE_CLASS where parent_id='" + targetNodeId + "' and ARCHIVE_CLASS_NODE_TYPE='2'");
        if (mapList!=null&&mapList.size()>0){
            jdbcAvicit.getJdbcTemplate().update("update  LANTOP_ARCHIVE_CLASS set parent_id='" + targetNodeId + "' where id ='" + treeId + "'");
            jdbcAvicit.getJdbcTemplate().update("update  LANTOP_ARCHIVE_CLASS set ARCHIVE_CLASS_NODE_PATH= (\t\n" +
                    "\tSELECT  SYS_CONNECT_BY_PATH(T.ID, '/') PH\n" +
                    "  FROM LANTOP_ARCHIVE_CLASS T\n" +
                    " where t.id = '" + treeId + "' \n" +
                    "CONNECT BY PRIOR T.ID = T.PARENT_ID  \n" +
                    " START WITH T.PARENT_ID = 'ROOT' \n" +
                    " ) where id ='" + treeId + "'");

        }else {
            reslut.put("msg","只能迁移文件夹节点下!");
        }
        return reslut;
    }


    @Override
    public List<TreeNode> findChildsByNodeId(String id) throws Exception{
        List<Map<String, Object>> mapList = jdbcAvicit.getJdbcTemplate().queryForList("WITH T1 AS\n" +
                " (SELECT T.*, SYS_CONNECT_BY_PATH(T.ID, '/') PH\n" +
                "    FROM LANTOP_ARCHIVE_CLASS T\n" +
                "  CONNECT BY PRIOR T.ID = T.PARENT_ID\n" +
                "   START WITH T.PARENT_ID = 'ROOT'),\n" +
                "T2 AS\n" +
                " (SELECT lac.*\n" +
                "    FROM LANTOP_ARCHIVE_CLASS LAC\n" +
                "   WHERE lac.id = '" + id + "'),\n" +
                "t3 as\n" +
                " (select t1.*\n" +
                "    from t1, t2\n" +
                "   where instr(t2.ARCHIVE_CLASS_NODE_PATH, t1.ph) > 0\n" +
                "     and t1.ARCHIVE_TYPE = '2'),\n" +
                "t4 as\n" +
                " (select t1.*\n" +
                "    from t1, t3\n" +
                "   where instr(t3.ARCHIVE_CLASS_NODE_PATH, t1.id) > 0\n" +
                "  union\n" +
                "  select t1.*\n" +
                "    from t1, t3\n" +
                "   where instr(t1.ph, t3.ARCHIVE_CLASS_NODE_PATH) > 0\n" +
                "     and t1.ARCHIVE_CLASS_NODE_TYPE = '1')\n" +
                "select * from t4\n");


        List<TreeNode> trees = new ArrayList<TreeNode>();
        if (mapList != null && mapList.size() > 0) {
            TreeNode tn = new TreeNode();
            for (Map<String, Object> map : mapList) {
                if ("ROOT".equals(map.get("PARENT_ID"))){
                    tn.setId(map.get("ID").toString());
                    tn.setAttributes(map);
                    tn.setIconCls("icon-im-newnorg");
                    tn.setText(map.get("ARCHIVE_CLASS_NODE_NAME").toString());
                    tn.setState("open");
                    tn.setChildren(RecursionOrgTree(mapList,map.get("ID").toString()));
                }

            }
            trees.add(tn);
        }

        return trees;
    }

    @Override
    public List<Map<String, Object>> findArchivePlan() {
        String sql = "SELECT PLAN_NAME FROM LANTOP_ARCHIVE_PLAN WHERE 1 = 1 AND PLAN_NAME IS NOT NULL";
        return this.jdbcAvicit.getJdbcTemplate().queryForList(sql);
    }

    @Override
    public ModelAndView getArchivePlanStatus(String nodeName, String orgId) throws Exception {
        ModelAndView mav = new ModelAndView();
        String sql = "SELECT NODE_NAME FROM LANTOP_ARCHIVE_PLAN_TREE WHERE QZH = '"+ orgId +"\' AND NODE_NAME IS NOT NULL";

        List<Map<String, Object>> list = this.jdbcAvicit.getJdbcTemplate().queryForList(sql);

        List<String> nameList = Lists.newArrayList();
        if (CommonFunction.listNotNull(list)){
            for (Map<String, Object> map : list){
                String noName = map.get("NODE_NAME").toString();
                nameList.add(noName);
            }
        }
        //判断该节点名称是否存在
        if (nameList.contains(nodeName)){
            mav.addObject("flag", 2);
        } else {
            mav.addObject("flag", 1);
        }

        return mav;
    }

    @Override
    public List<Map<String, Object>> getUserInfoByLoginName(String userName) {
        StringBuffer sql = new StringBuffer();
        sql.append("select su.ID userId,sudp.ORG_IDENTITY orgId,sudp.DEPT_ID deptId,so.ORG_CODE orgCode,su.SECRET_LEVEL secretLevel   \n");
        sql.append(        "from SYS_USER su left join  SYS_USER_DEPT_POSITION sudp on su.ID = sudp.USER_ID \n" );
        sql.append(        " left join SYS_ORG so on sudp.ORG_IDENTITY = so.ID  \n" );
        sql.append(        "where su.LOGIN_NAME = ?  and sudp.is_chief_dept = '1'" );

        List<Map<String, Object>> list = this.jdbcAvicit.getJdbcTemplate().queryForList(sql.toString(),userName);

        return list;
    }

    @Override
    public Map<String, Object> getDealt(String userId, String taskTitle, String taskFinished, String falg, String orgId, Integer page, Integer rows,String begin,String end )throws Exception {
        Map<String, Object> result = new HashMap<>();

        String sql = "select count(1) from (SELECT MAX(t3.APPLY_TITLE)  \"taskTitle\" , t.EXECUTION_,t.PROCINST_,t.TASK_B_ID_ FROM BPM_HIST_TASK_V t   LEFT JOIN BPM_ENTRY_V t1 ON t.TASK_B_ID_=t1.FORMID_  inner join CBB_MY_UNDO_APPLY  t3 ON t3.FORM_ID = t.TASK_B_ID_";
        sql += " WHERE  (t3.ORG_ID='" + orgId + "'  OR  t3.ATTRIBUTE_08 IS NOT NULL) AND t.TASK_STATE_='1' AND regexp_substr(t.FORM_CODE_,'[^_]+',1,2) in ('DeptdocarrangeSubmitNew','selectuser','paperTechnologicalprocess','SpecialArchiveResearch','PersonalArchiveResearch','ArchiveIdentifySubmit') AND t.TASK_FINISHED_='" + taskFinished + "' AND t.ASSIGNEE_ ='" + userId + "' ";
        if ("1".equals(falg)) {
            sql += "AND t1.BUSINESSSTATE_ <> 'ended' ";
        }
        if ("2".equals(falg)) {
            sql += "AND t1.BUSINESSSTATE_= 'ended' ";
        }

        if (StringUtils.isNotBlank(begin)){
            sql += " AND TO_TIMESTAMP( '"+begin+"', ' yyyy-MM-dd hh24:mi:ss' )  <= t.CREATE_";
        }
        if (StringUtils.isNotBlank(end)){
            sql += " AND  t.CREATE_ <= TO_TIMESTAMP( '"+end+"', ' yyyy-MM-dd hh24:mi:ss' )";
        }

        sql += " GROUP BY t.EXECUTION_,t.PROCINST_,t.TASK_B_ID_ ) A1 WHERE 1=1 ";
        if (taskTitle != null && !taskTitle.equals(ComUtil.EMPTY)) {
            sql += " AND A1.\"taskTitle\" like '%" + taskTitle + "%'";
        }

        Long total = this.jdbcAvicit.getJdbcTemplate().queryForLong(sql);
        System.out.println("查询数量sql:"+sql);
        result.put("total", total);

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM (");
        sbSql.append(" SELECT ROW_NUMBER () OVER ( ORDER BY A1.\"cTime\" DESC ) \"sort\",A1.* FROM (");
        sbSql.append("SELECT  MAX(t.DBID_) \"dbid\", MAX(t3.APPLY_TITLE) \"taskTitle\",t.EXECUTION_ \"executionId\",MAX(t.CREATE_) \"cTime\",MAX(t.OUTCOME_)  \"outcome\",t.PROCINST_ \"processInstance\",t.TASK_B_ID_ \"formId\",MAX(t4.NAME) \"taskSendUser\",MAX(t.TASK_SENDDEPT_) \"taskSendDept\",MAX(t.PROCESS_DEF_NAME_) \"processDefName\",MAX(t1.BUSINESSSTATE_) BUSINESSSTATE_,MAX(t1.activityname_) \"targetActivityName\",MAX( t.FORM_CODE_ ) \"formCode\"  FROM BPM_HIST_TASK_V t   LEFT JOIN BPM_ENTRY_V t1 ON t.TASK_B_ID_=t1.FORMID_  inner join CBB_MY_UNDO_APPLY t3 ON t3.FORM_ID = t.TASK_B_ID_  LEFT JOIN SYS_USER t4 ON t4.id=t3.CREATED_BY ");
        sbSql.append(" WHERE  (t3.ORG_ID='" + orgId + "' OR  t3.ATTRIBUTE_08 IS NOT NULL) AND t.TASK_STATE_='1' AND regexp_substr(t.FORM_CODE_,'[^_]+',1,2) in ('DeptdocarrangeSubmitNew','selectuser','paperTechnologicalprocess','SpecialArchiveResearch','PersonalArchiveResearch','ArchiveIdentifySubmit') AND t.TASK_FINISHED_='");
        sbSql.append(taskFinished);
        sbSql.append("' AND t.ASSIGNEE_ ='");
        sbSql.append(userId);
        sbSql.append("' ");
        if ("1".equals(falg)) {
            sbSql.append(" AND t1.BUSINESSSTATE_<>'ended' ");
        }
        if ("2".equals(falg)) {
            sbSql.append(" AND t1.BUSINESSSTATE_='ended' ");
        }

        sbSql.append(" GROUP BY t.EXECUTION_,t.PROCINST_,t.TASK_B_ID_) A1 WHERE 1=1 ");

        if (taskTitle != null && !taskTitle.equals(ComUtil.EMPTY)) {
            sbSql.append(" AND A1.\"taskTitle\" LIKE '%" + taskTitle + "%' ");
        }

        if (StringUtils.isNotBlank(begin)){
            sbSql.append(" AND TO_TIMESTAMP( '"+begin+"', 'yyyy-MM-dd hh24:mi:ss' )  <= A1.\"cTime\"");
        }
        if (StringUtils.isNotBlank(end)){
            sbSql.append( " AND A1.\"cTime\" <= TO_TIMESTAMP( '"+end+"', 'yyyy-MM-dd hh24:mi:ss' )");
        }

        sbSql.append(") ROW_ WHERE ROWNUM <");
        sbSql.append(page * rows + 1);

        sbSql.append(" ) WHERE ROWNUM_ >");
        sbSql.append((page - 1) * rows);

        System.out.println("查询列表sql:"+ sbSql.toString());
        List<Map<String, Object>> maps = jdbcAvicit.getJdbcTemplate().queryForList(sbSql.toString());

        if(maps != null && maps.size()>0){
            for (Map<String, Object> map : maps) {
                //字典翻译
                String state = (String)map.get("BUSINESSSTATE_");
                if("start".equals(state)){
                    map.put("BUSINESSSTATE_","拟稿中");
                }else if("active".equals(state)){
                    map.put("BUSINESSSTATE_","流转中");
                }else if("ended".equals(state)){
                    map.put("BUSINESSSTATE_","已完结");
                }

                String cTime = String.valueOf(map.get("cTime"));
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                map.put("cTime",format.format(format.parse(cTime)));
            }
        }


        result.put("rows", maps);
        return result;
    }

    /**
     * 使用递归方法建组织树
     * @param
     * @return
     */
    public List<TreeNode> RecursionOrgTree(List<Map<String, Object>> mapList, String nodeId) {
        List<TreeNode> trees = new ArrayList<TreeNode>();
        TreeNode tn = new TreeNode();
        for (Map<String, Object> map : mapList) {
            if (nodeId.equals(map.get("PARENT_ID"))){
                tn.setId(map.get("ID").toString());
                tn.setAttributes(map);
                tn.setIconCls("icon-im-newnmodulelib");
                tn.setText(map.get("ARCHIVE_CLASS_NODE_NAME").toString());
                tn.setState("open");
                tn.setChildren(RecursionOrgTree(mapList,map.get("ID").toString()));
                trees.add(tn);
            }
        }
        return trees;
    }


}
